<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
 * <http://www.doctrine-project.org>.
 */

namespace Doctrine\DBAL\Platforms;

use Doctrine\DBAL\Schema\ForeignKeyConstraint;

use Doctrine\DBAL\DBALException, Doctrine\DBAL\Schema\TableDiff;

/**
 * The MySqlPlatform provides the behavior, features and SQL dialect of the
 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that
 * uses the InnoDB storage engine.
 *
 * @since 2.0
 * @author Roman Borschel <roman@code-factory.org>
 * @author Benjamin Eberlei <kontakt@beberlei.de>
 * @todo Rename: MySQLPlatform
 */
class MySqlPlatform extends AbstractPlatform {
	//XXX added to support comment only in sql
	public function getCommentValueDeclarationSQL($field) {
		if( isset( $field ) ) {
			$comment = "COMMENT '$field'";
		} else {
			$comment = '';
		}
		return $comment;
	}
	
	/**
	 * Gets the character used for identifier quoting.
	 *
	 * @return string
	 * @override
	 */
	public function getIdentifierQuoteCharacter() {
		return '`';
	}
	
	/**
	 * Returns the regular expression operator.
	 *
	 * @return string
	 * @override
	 */
	public function getRegexpExpression() {
		return 'RLIKE';
	}
	
	/**
	 * Returns global unique identifier
	 *
	 * @return string to get global unique identifier
	 * @override
	 */
	public function getGuidExpression() {
		return 'UUID()';
	}
	
	/**
	 * returns the position of the first occurrence of substring $substr in string $str
	 *
	 * @param string $substr    literal string to find
	 * @param string $str       literal string
	 * @param int    $pos       position to start at, beginning of string by default
	 * @return integer
	 */
	public function getLocateExpression($str, $substr, $startPos = false) {
		if( $startPos == false ) {
			return 'LOCATE(' . $substr . ', ' . $str . ')';
		} else {
			return 'LOCATE(' . $substr . ', ' . $str . ', ' . $startPos . ')';
		}
	}
	
	/**
	 * Returns a series of strings concatinated
	 *
	 * concat() accepts an arbitrary number of parameters. Each parameter
	 * must contain an expression or an array with expressions.
	 *
	 * @param string|array(string) strings that will be concatinated.
	 * @override
	 */
	public function getConcatExpression() {
		$args = func_get_args();
		return 'CONCAT(' . join( ', ', ( array ) $args ) . ')';
	}
	
	public function getListDatabasesSQL() {
		return 'SHOW DATABASES';
	}
	
	public function getListTableConstraintsSQL($table) {
		return 'SHOW INDEX FROM ' . $table;
	}
	
	/**
	 * Two approaches to listing the table indexes. The information_schema is 
	 * prefered, because it doesn't cause problems with SQL keywords such as "order" or "table".
	 * 
	 * @param string $table
	 * @param string $currentDatabase
	 * @return string
	 */
	public function getListTableIndexesSQL($table, $currentDatabase = null) {
		if( $currentDatabase ) {
			return "SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, " . "SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, " . "CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, " . "NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment " . "FROM information_schema.STATISTICS WHERE TABLE_NAME = '" . $table . "' AND TABLE_SCHEMA = '" . $currentDatabase . "'";
		} else {
			return 'SHOW INDEX FROM ' . $table;
		}
	}
	
	public function getListViewsSQL($database) {
		return "SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '" . $database . "'";
	}
	
	public function getListTableForeignKeysSQL($table, $database = null) {
		$sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, " . "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ " . "FROM information_schema.key_column_usage k /*!50116 " . "INNER JOIN information_schema.referential_constraints c ON " . "  c.constraint_name = k.constraint_name AND " . "  c.table_name = '$table' */ WHERE k.table_name = '$table'";
		
		if( $database ) {
			$sql .= " AND k.table_schema = '$database' /*!50116 AND c.constraint_schema = '$database' */";
		}
		
		$sql .= " AND k.`REFERENCED_COLUMN_NAME` is not NULL";
		
		return $sql;
	}
	
	public function getCreateViewSQL($name, $sql) {
		return 'CREATE VIEW ' . $name . ' AS ' . $sql;
	}
	
	public function getDropViewSQL($name) {
		return 'DROP VIEW ' . $name;
	}
	
	/**
	 * Gets the SQL snippet used to declare a VARCHAR column on the MySql platform.
	 *
	 * @params array $field
	 */
	protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) {
		return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)') : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
	}
	
	/** @override */
	public function getClobTypeDeclarationSQL(array $field) {
		if( !empty( $field['length'] ) && is_numeric( $field['length'] ) ) {
			$length = $field['length'];
			if( $length <= 255 ) {
				return 'TINYTEXT';
			} else if( $length <= 65532 ) {
				return 'TEXT';
			} else if( $length <= 16777215 ) {
				return 'MEDIUMTEXT';
			}
		}
		return 'LONGTEXT';
	}
	
	/**
	 * @override
	 */
	public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration) {
		if( isset( $fieldDeclaration['version'] ) && $fieldDeclaration['version'] == true ) {
			return 'TIMESTAMP';
		} else {
			return 'DATETIME';
		}
	}
	
	/**
	 * @override
	 */
	public function getDateTypeDeclarationSQL(array $fieldDeclaration) {
		return 'DATE';
	}
	
	/**
	 * @override
	 */
	public function getTimeTypeDeclarationSQL(array $fieldDeclaration) {
		return 'TIME';
	}
	
	/**
	 * @override
	 */
	public function getBooleanTypeDeclarationSQL(array $field) {
		return 'TINYINT(1)';
	}
	
	/**
	 * Obtain DBMS specific SQL code portion needed to set the COLLATION
	 * of a field declaration to be used in statements like CREATE TABLE.
	 *
	 * @param string $collation   name of the collation
	 * @return string  DBMS specific SQL code portion needed to set the COLLATION
	 * of a field declaration.
	 */
	public function getCollationFieldDeclaration($collation) {
		return 'COLLATE ' . $collation;
	}
	
	/**
	 * Whether the platform prefers identity columns for ID generation.
	 * MySql prefers "autoincrement" identity columns since sequences can only
	 * be emulated with a table.
	 *
	 * @return boolean
	 * @override
	 */
	public function prefersIdentityColumns() {
		return true;
	}
	
	/**
	 * Whether the platform supports identity columns.
	 * MySql supports this through AUTO_INCREMENT columns.
	 *
	 * @return boolean
	 * @override
	 */
	public function supportsIdentityColumns() {
		return true;
	}
	
	public function getShowDatabasesSQL() {
		return 'SHOW DATABASES';
	}
	
	public function getListTablesSQL() {
		return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'";
	}
	
	//XXX modified to add comment support
	public function getListTableColumnsSQL($table) {
		return "SHOW FULL COLUMNS FROM $table";
	}
//	public function getListTableColumnsSQL($table) {
//		return 'DESCRIBE ' . $table;
//	}
	
	/**
	 * create a new database
	 *
	 * @param string $name name of the database that should be created
	 * @return string
	 * @override
	 */
	public function getCreateDatabaseSQL($name) {
		return 'CREATE DATABASE ' . $name;
	}
	
	/**
	 * drop an existing database
	 *
	 * @param string $name name of the database that should be dropped
	 * @return string
	 * @override
	 */
	public function getDropDatabaseSQL($name) {
		return 'DROP DATABASE ' . $name;
	}
	
	/**
	 * create a new table
	 *
	 * @param string $tableName   Name of the database that should be created
	 * @param array $columns  Associative array that contains the definition of each field of the new table
	 * The indexes of the array entries are the names of the fields of the table an
	 * the array entry values are associative arrays like those that are meant to be
	 * passed with the field definitions to get[Type]Declaration() functions.
	 * array(
	 * 'id' => array(
	 * 'type' => 'integer',
	 * 'unsigned' => 1
	 * 'notnull' => 1
	 * 'default' => 0
	 * ),
	 * 'name' => array(
	 * 'type' => 'text',
	 * 'length' => 12
	 * ),
	 * 'password' => array(
	 * 'type' => 'text',
	 * 'length' => 12
	 * )
	 * );
	 * @param array $options  An associative array of table options:
	 * array(
	 * 'comment' => 'Foo',
	 * 'charset' => 'utf8',
	 * 'collate' => 'utf8_unicode_ci',
	 * 'type'    => 'innodb',
	 * );
	 *
	 * @return void
	 * @override
	 */
	protected function _getCreateTableSQL($tableName, array $columns, array $options = array()) {
		$queryFields = $this->getColumnDeclarationListSQL( $columns );
		
		if( isset( $options['uniqueConstraints'] ) && !empty( $options['uniqueConstraints'] ) ) {
			foreach( $options['uniqueConstraints'] as $index => $definition ) {
				$queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL( $index, $definition );
			}
		}
		
		// add all indexes
		if( isset( $options['indexes'] ) && !empty( $options['indexes'] ) ) {
			foreach( $options['indexes'] as $index => $definition ) {
				$queryFields .= ', ' . $this->getIndexDeclarationSQL( $index, $definition );
			}
		}
		
		// attach all primary keys
		if( isset( $options['primary'] ) && !empty( $options['primary'] ) ) {
			$keyColumns = array_unique( array_values( $options['primary'] ) );
			$queryFields .= ', PRIMARY KEY(' . implode( ', ', $keyColumns ) . ')';
		}
		
		$query = 'CREATE ';
		if( !empty( $options['temporary'] ) ) {
			$query .= 'TEMPORARY ';
		}
		$query .= 'TABLE ' . $tableName . ' (' . $queryFields . ')';
		
		$optionStrings = array();
		//XXX HERE is the point for table comment !!!! just come back after
		if( isset( $options['comment'] ) ) {
			$optionStrings['comment'] = 'COMMENT = ' . $options['comment'];
		}
		if( isset( $options['charset'] ) ) {
			$optionStrings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
			if( isset( $options['collate'] ) ) {
				$optionStrings['charset'] .= ' COLLATE ' . $options['collate'];
			}
		}
		
		// get the type of the table
		if( isset( $options['engine'] ) ) {
			$optionStrings[] = 'ENGINE = ' . $options['engine'];
		} else {
			// default to innodb
			$optionStrings[] = 'ENGINE = InnoDB';
		}
		
		if( !empty( $optionStrings ) ) {
			$query .= ' ' . implode( ' ', $optionStrings );
		}
		$sql[] = $query;
		
		if( isset( $options['foreignKeys'] ) ) {
			foreach( ( array ) $options['foreignKeys'] as $definition ) {
				$sql[] = $this->getCreateForeignKeySQL( $definition, $tableName );
			}
		}
		
		return $sql;
	}
	
	/**
	 * Gets the SQL to alter an existing table.
	 *
	 * @param TableDiff $diff
	 * @return array
	 */
	public function getAlterTableSQL(TableDiff $diff) {
		$queryParts = array();
		if( $diff->newName !== false ) {
			$queryParts[] = 'RENAME TO ' . $diff->newName;
		}
		
		foreach( $diff->addedColumns as $fieldName => $column ) {
			$queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL( $column->getQuotedName( $this ), $column->toArray() );
		}
		
		foreach( $diff->removedColumns as $column ) {
			$queryParts[] = 'DROP ' . $column->getQuotedName( $this );
		}
		
		foreach( $diff->changedColumns as $columnDiff ) {
			/* @var $columnDiff Doctrine\DBAL\Schema\ColumnDiff */
			$column = $columnDiff->column;
			$queryParts[] = 'CHANGE ' . ($columnDiff->oldColumnName) . ' ' . $this->getColumnDeclarationSQL( $column->getQuotedName( $this ), $column->toArray() );
		}
		
		foreach( $diff->renamedColumns as $oldColumnName => $column ) {
			$queryParts[] = 'CHANGE ' . $oldColumnName . ' ' . $this->getColumnDeclarationSQL( $column->getQuotedName( $this ), $column->toArray() );
		}
		
		$sql = array();
		if( count( $queryParts ) > 0 ) {
			$sql[] = 'ALTER TABLE ' . $diff->name . ' ' . implode( ", ", $queryParts );
		}
		$sql = array_merge( $sql, $this->_getAlterTableIndexForeignKeySQL( $diff ) );
		return $sql;
	}
	
	/**
	 * Obtain DBMS specific SQL code portion needed to declare an integer type
	 * field to be used in statements like CREATE TABLE.
	 *
	 * @param string  $name   name the field to be declared.
	 * @param string  $field  associative array with the name of the properties
	 * of the field being declared as array indexes.
	 * Currently, the types of supported field
	 * properties are as follows:
	 *
	 * unsigned
	 * Boolean flag that indicates whether the field
	 * should be declared as unsigned integer if
	 * possible.
	 *
	 * default
	 * Integer value to be used as default for this
	 * field.
	 *
	 * notnull
	 * Boolean flag that indicates whether this field is
	 * constrained to not be set to null.
	 * @return string  DBMS specific SQL code portion that should be used to
	 * declare the specified field.
	 * @override
	 */
	public function getIntegerTypeDeclarationSQL(array $field) {
		return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL( $field );
	}
	
	/** @override */
	public function getBigIntTypeDeclarationSQL(array $field) {
		return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL( $field );
	}
	
	/** @override */
	public function getSmallIntTypeDeclarationSQL(array $field) {
		return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL( $field );
	}
	
	/** @override */
	protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef) {
		$autoinc = '';
		if( !empty( $columnDef['autoincrement'] ) ) {
			$autoinc = ' AUTO_INCREMENT';
		}
		$unsigned = (isset( $columnDef['unsigned'] ) && $columnDef['unsigned']) ? ' UNSIGNED' : '';
		
		return $unsigned . $autoinc;
	}
	
	/**
	 * Return the FOREIGN KEY query section dealing with non-standard options
	 * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
	 *
	 * @param ForeignKeyConstraint $foreignKey
	 * @return string
	 * @override
	 */
	public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey) {
		$query = '';
		if( $foreignKey->hasOption( 'match' ) ) {
			$query .= ' MATCH ' . $foreignKey->getOption( 'match' );
		}
		$query .= parent::getAdvancedForeignKeyOptionsSQL( $foreignKey );
		return $query;
	}
	
	/**
	 * Gets the SQL to drop an index of a table.
	 *
	 * @param Index $index           name of the index to be dropped
	 * @param string|Table $table          name of table that should be used in method
	 * @override
	 */
	public function getDropIndexSQL($index, $table = null) {
		if( $index instanceof \Doctrine\DBAL\Schema\Index ) {
			$index = $index->getQuotedName( $this );
		} else if( !is_string( $index ) ) {
			throw new \InvalidArgumentException( 'MysqlPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.' );
		}
		
		if( $table instanceof \Doctrine\DBAL\Schema\Table ) {
			$table = $table->getQuotedName( $this );
		} else if( !is_string( $table ) ) {
			throw new \InvalidArgumentException( 'MysqlPlatform::getDropIndexSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.' );
		}
		
		return 'DROP INDEX ' . $index . ' ON ' . $table;
	}
	
	/**
	 * Gets the SQL to drop a table.
	 *
	 * @param string $table The name of table to drop.
	 * @override
	 */
	public function getDropTableSQL($table) {
		if( $table instanceof \Doctrine\DBAL\Schema\Table ) {
			$table = $table->getQuotedName( $this );
		} else if( !is_string( $table ) ) {
			throw new \InvalidArgumentException( 'MysqlPlatform::getDropTableSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.' );
		}
		
		return 'DROP TABLE ' . $table;
	}
	
	public function getSetTransactionIsolationSQL($level) {
		return 'SET SESSION TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL( $level );
	}
	
	/**
	 * Get the platform name for this instance.
	 *
	 * @return string
	 */
	public function getName() {
		return 'mysql';
	}
	
	public function getReadLockSQL() {
		return 'LOCK IN SHARE MODE';
	}
	
	protected function initializeDoctrineTypeMappings() {
		$this->doctrineTypeMapping = array( 'tinyint' => 'boolean', 'smallint' => 'smallint', 'mediumint' => 'integer', 'int' => 'integer', 'integer' => 'integer', 'bigint' => 'bigint', 'tinytext' => 'text', 'mediumtext' => 'text', 'longtext' => 'text', 'text' => 'text', 'varchar' => 'string', 'string' => 'string', 'char' => 'string', 'date' => 'date', 'datetime' => 'datetime', 'timestamp' => 'datetime', 'time' => 'time', 'float' => 'float', 'double' => 'float', 'real' => 'float', 'decimal' => 'decimal', 'numeric' => 'decimal', 'year' => 'date' );
	}
	
	public function getVarcharMaxLength() {
		return 65535;
	}
}
